﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlTypes;
using DTO;


namespace DAO
{
    public class KhachHangDAO
    {
        public static KhachHangDTO ThongTinTaiKhoan(int makhachhang)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@makhachhang", makhachhang));
            DataTable dataTable = new DataTable();
            dataTable = DataProvider.ExecuteReader("usp_ThongTinTaiKhoan", sqlParams);
            KhachHangDTO khachhangDTO;
            if (dataTable.Rows.Count > 0)
            {
                DataRow dataRow = dataTable.Rows[0];
                khachhangDTO = new KhachHangDTO();
                khachhangDTO.MaKhachHang = int.Parse(dataRow["MaKhachHang"].ToString());
                khachhangDTO.MatKhau = dataRow["MatKhau"].ToString();
                khachhangDTO.Email = (dataRow["Email"].ToString());
                khachhangDTO.EmailPhu = (dataRow["EmailPhu"].ToString());
                khachhangDTO.TenKhachHang = (dataRow["TenKhachHang"].ToString());
                khachhangDTO.DiaChi = (dataRow["DiaChi"].ToString());
                khachhangDTO.DienThoai = (dataRow["DienThoai"].ToString());
                khachhangDTO.TinhTrang = (dataRow["TinhTrang"].ToString());

            }
            else
                khachhangDTO = null;
            return khachhangDTO;
        }

		public static KhachHangDTO DangNhap(string email, string matkhau)
        {
            //usp_DangNhap @email varchar(50) , @matkhau varchar(50), @manhanvien int, @flag int
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@email", email));
            sqlParams.Add(new SqlParameter("@matkhau", matkhau));
            sqlParams.Add(new SqlParameter("@manhanvien",""));
            sqlParams.Add(new SqlParameter("@flag", 2));
            DataTable dataTable = new DataTable();
            dataTable = DataProvider.ExecuteReader("usp_DangNhap", sqlParams);
            KhachHangDTO khachhangDTO;
            if (dataTable.Rows.Count > 0)
            {
                DataRow dataRow = dataTable.Rows[0];
                khachhangDTO = new KhachHangDTO();
                khachhangDTO.MaKhachHang = int.Parse(dataRow["MaKhachHang"].ToString());
                khachhangDTO.MatKhau = dataRow["MatKhau"].ToString();
                khachhangDTO.Email = (dataRow["Email"].ToString());
                khachhangDTO.EmailPhu = (dataRow["EmailPhu"].ToString());
                khachhangDTO.TenKhachHang = (dataRow["TenKhachHang"].ToString());
                khachhangDTO.DiaChi = (dataRow["DiaChi"].ToString());
                khachhangDTO.DienThoai = (dataRow["DienThoai"].ToString());
                khachhangDTO.TinhTrang = (dataRow["TinhTrang"].ToString());

            }
            else
                khachhangDTO = null;
            return khachhangDTO;
        }

        public static List<KhachHangDTO> DanhSachTaiKhoan(string HoTen, string TinhTrang, ref int dem)
        {
            //usp_DanhSachTaiKhoan ,@HoTen nvarchar(50) = null, 
            //@TinhTrang nvarchar(20) = null
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            string sqlSelect, sqlInsert, sqlUpdate, sqlCount;
            string sqlWhere = "";
            string temp = "";
            SqlCommand cmdSelect, cmdInsert, cmdUpdate;

            sqlCount = "select count(kh.MaKhachHang) from KhachHang kh ";
            sqlSelect = "select kh.* from KhachHang kh ";

            List<string> lstString = new List<string>();
            if (HoTen != "")
            {
                temp = " (TenKhachHang like N'%" + HoTen +"%') ";
                lstString.Add(temp);    
            }
            if (TinhTrang != "")
            {
                temp = " (TinhTrang like N'"+TinhTrang+"') ";
                lstString.Add(temp);    
            }
            if (lstString.Count > 0)
            {
                sqlWhere = sqlWhere +" where " + lstString[0];
                for (int i = 1; i < lstString.Count; i++)
                {
                    sqlWhere = sqlWhere + " and " + lstString[i];
                }
            }
            sqlCount += sqlWhere;
            sqlSelect += sqlWhere;
            connect.Open();
            SqlTransaction tran = connect.BeginTransaction();
            cmdSelect = new SqlCommand(sqlCount, connect, tran);
            dem = (int)cmdSelect.ExecuteScalar();//lấy số lượng kết quả
            cmdSelect = new SqlCommand(sqlSelect, connect, tran);
            SqlDataAdapter adapter = new SqlDataAdapter(cmdSelect);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);

            List<KhachHangDTO> lstkhDTO = new List<KhachHangDTO>();
            if (dataTable.Rows.Count > 0)
            {
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    KhachHangDTO khDTO = new KhachHangDTO();
                    khDTO.MaKhachHang = int.Parse(dataRow["MaKhachHang"].ToString());
                    khDTO.MatKhau = dataRow["MatKhau"].ToString();
                    khDTO.Email = (dataRow["Email"].ToString());
                    khDTO.EmailPhu = (dataRow["EmailPhu"].ToString());
                    khDTO.TenKhachHang = (dataRow["TenKhachHang"].ToString());
                    khDTO.DiaChi = (dataRow["DiaChi"].ToString());
                    khDTO.DienThoai = (dataRow["DienThoai"].ToString());
                    khDTO.TinhTrang = (dataRow["TinhTrang"].ToString());
                    lstkhDTO.Add(khDTO);
                }
            }
            else
                lstkhDTO = null;
            return lstkhDTO;
        }

        public static bool DangKy(string Email, string MatKhau, string EmailPhu, string TenKhachHang, string DiaChi, string DienThoai)
        {
            //@email varchar(50) = null, matkhau varchar(50) = null, emailphu varchar(50) = null, 	
            //@tenkhachhang nvarchar(50) = null,	@diachi nvarchar(50) = null,@dienthoai char(11)= null
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@email", Email));
            sqlParams.Add(new SqlParameter("@matkhau", MatKhau));
            sqlParams.Add(new SqlParameter("@emailphu", EmailPhu));
            sqlParams.Add(new SqlParameter("@tenkhachhang", TenKhachHang));
            sqlParams.Add(new SqlParameter("@diachi", DiaChi));
            sqlParams.Add(new SqlParameter("@dienthoai", DienThoai));
            int kq = (int)DataProvider.ExecuteScalar("usp_DangKy", sqlParams);
            if (kq > 0)
                return true;
            return false;
        }

        public static bool CapNhatTaiKhoan(int makhachhang, string tinhtrang)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@MaKH", makhachhang));
            sqlParams.Add(new SqlParameter("@TinhTrang", tinhtrang));

            int kq = (int)DataProvider.ExecuteScalar("usp_CapNhatTaiKhoan", sqlParams);
            if (kq == 0)
            {
                return false;
            }
            return true;

         
        }
    }
}
